Quering alert log using SQL query
Oracle 11g开始,提供了X$DBGALERTEXT,可以用来使用sql访问数据库alert日志,在不方便直接访问服务器时,提供了另外的方式访问数据库alert日志。X$DBGALERTEXT的定义如下:
SYS> desc X$DBGALERTEXT
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(64)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(64)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
SUPPLEMENTAL_DETAILS VARCHAR2(128)
PARTITION NUMBER
RECORD_ID NUMBER
比较常用的SQL如下(可以得到类似直接访问文本格式alert日志样式的结果):
SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from X$DBGALERTEXT;
Finding Trace File Path using SQL
11g开始,Oracle提供了X$DBGDIREXT接口,可以用来查看diagnostic_dest下的目录和文件。X$DBGDIREXT的定义如下:
SYS> desc X$DBGDIREXT;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
PHYSICAL_PATH VARCHAR2(444)
LOGICAL_PATH VARCHAR2(444)
PHYSICAL_FILE VARCHAR2(68)
LOGICAL_FILE VARCHAR2(68)
CREATION_TIME TIMESTAMP(3) WITH TIME ZONE
MODIFY_TIME TIMESTAMP(3) WITH TIME ZONE
LVL NUMBER
TYPE NUMBER
一个非常handy的用法:有时候不熟悉Oracle的人,我们需要它帮忙把alert日志取出来的时候,由于11g的alert是放在diagnostic中的,描述日志的路径每次都要费很大的劲,现在我们可以用下面sql来直接获取到alert日志的路径了:
SELECT PHYSICAL_PATH || CHR(47) || PHYSICAL_FILE
FROM X$DBGDIREXT
WHERE 1 = 1
AND PHYSICAL_FILE LIKE 'alert_%'
AND PHYSICAL_PATH LIKE '%rdbms%'
/
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。